﻿// See https://aka.ms/new-console-template for more information
using Microsoft.Data.SqlClient;
using System.Diagnostics;

Stopwatch stopwatch = Stopwatch.StartNew();

for (int i = 0; i < 10000; i++)
{
    var result = SqlHelper.GetEntity<UserEntiry>(7);
}
stopwatch.Stop();

Console.WriteLine($"反射查询1万次耗时：{stopwatch.ElapsedMilliseconds}");

stopwatch.Restart();
for (int i = 0; i < 10000; i++)
{
    var result = SqlHelper.GetEntity2<UserEntiry>(7);
}
stopwatch.Stop();

Console.WriteLine($"泛型缓存优化后查询1万次耗时：{stopwatch.ElapsedMilliseconds}");
Console.WriteLine("Hello, World!");

public class UserEntiry
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Age { get; set; }
    public string Gender { get; set; }
    public string Birthday { get; set; }
    public string Address { get; set; }
}

public class SqlHelper
{
    private static string ConnectionString = "server=.;database=commonbackend;uid=sa;pwd=12345678;TrustServerCertificate=true;";

    public static T GetEntity<T>(int Id) where T : new()
    {
        T obj = new();
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            string sqlcommand = $"select {string.Join(",", typeof(T).GetProperties().Select(x => $"[{x.Name}]"))} from {typeof(T).Name} where Id='{Id}'";
            // 创建命令对象
            SqlCommand command = new SqlCommand(sqlcommand, connection);
            // 执行命令并读取结果
            SqlDataReader reader = command.ExecuteReader();
            foreach (var prop in typeof(T).GetProperties())
            {
                if (reader.Read())
                {
                    object value = reader[prop.Name];
                    if (value != DBNull.Value)
                    {
                        prop.SetValue(obj, reader[prop.Name]);
                    }
                }
            }

            connection.Close();
        }
        return obj;
    }

    public static T GetEntity2<T>(int Id) where T : new()
    {
        T obj = new();
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            string sqlcommand = $"{GenericStaticClass<T>.GetQueryString()} where Id='{Id}'";
            // 创建命令对象
            SqlCommand command = new SqlCommand(sqlcommand, connection);
            // 执行命令并读取结果
            SqlDataReader reader = command.ExecuteReader();
            foreach (var prop in typeof(T).GetProperties())
            {
                if (reader.Read())
                {
                    object value = reader[prop.Name];
                    if (value != DBNull.Value)
                    {
                        prop.SetValue(obj, reader[prop.Name]);
                    }
                }
            }

            connection.Close();
        }
        return obj;
    }
}

//泛型缓存优化类
public static class GenericStaticClass<T>
{
    private static string _queryString;

    static GenericStaticClass()
    {
        _queryString = $"select {string.Join(",", typeof(T).GetProperties().Select(x => $"[{x.Name}]"))} from {typeof(T).Name}";
    }

    public static string GetQueryString()
    {
        return _queryString;
    }
}